Option Explicit

Sub SendMyDExlToGoogle()
    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim data As Variant
    Dim jsonBody As String
    Dim win As Object
    Dim webUrl As String
    
    On Error GoTo ErrorHandler
    
    Set ws = ThisWorkbook.Worksheets("MyDExl")
    lastRow = ws.Cells(ws.rows.count, "A").End(xlUp).row
    lastCol = ws.Cells(1, ws.Columns.count).End(xlToLeft).Column
    
    If lastRow < 1 Or lastCol < 1 Then
        MsgBox "No data found in MyDExl sheet"
        Exit Sub
    End If
    
    data = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)).value
    jsonBody = "{""values"":" & TwoDArrayToJsonSafe(data) & "}"
    
    webUrl = Trim$(ThisWorkbook.Worksheets("MasterCode").Range("D4").text)
    
    Set win = CreateObject("WinHttp.WinHttpRequest.5.1")
    win.Open "POST", webUrl, False
    win.setRequestHeader "Content-Type", "application/json"
    win.send jsonBody
    
    If win.Status = 200 Then
        MsgBox "? SUCCESS! " & lastRow & "x" & lastCol & " data sent to Google Sheet."
    Else
        MsgBox "? HTTP Error " & win.Status & vbCrLf & win.responseText
    End If
    
    Exit Sub
    
ErrorHandler:
    MsgBox "? VBA Error " & Err.Number & ": " & Err.Description
End Sub

Private Function TwoDArrayToJsonSafe(arr As Variant) As String
    Dim r As Long, c As Long
    Dim rows() As String, cols() As String
    Dim cellVal As String
    
    ReDim rows(1 To UBound(arr, 1))
    
    For r = 1 To UBound(arr, 1)
        ReDim cols(1 To UBound(arr, 2))
        For c = 1 To UBound(arr, 2)
            cellVal = CleanJsonString(CStr(arr(r, c)))
            cols(c) = cellVal
        Next c
        rows(r) = "[" & Join(cols, ",") & "]"
    Next r
    
    TwoDArrayToJsonSafe = "[" & Join(rows, ",") & "]"
End Function

Private Function CleanJsonString(inputStr As String) As String
    Dim i As Long
    Dim output As String
    Dim ch As String
    
    output = ""
    For i = 1 To Len(inputStr)
        ch = Mid(inputStr, i, 1)
        
        Select Case ch
            Case """"
                output = output & "\"""
            Case "\"
                output = output & "\\"
            Case vbCr
                output = output & "\r"
            Case vbLf
                output = output & "\n"
            Case vbTab
                output = output & "\t"
            Case Else
                output = output & ch
        End Select
    Next i
    
    CleanJsonString = """" & output & """"
End Function
###############################################################################



' ============================================
' Excel VBA - Google Sheets Import
' WITH TLS 1.2 FIX FOR "ACCESS DENIED" ERROR
' ============================================

Option Explicit

' ========== CONFIGURATION ==========
Const WEB_APP_URL As String = "Please_Enter_Web_App_URL"

' Windows API declarations for enabling TLS 1.2
#If VBA7 Then
    Private Declare PtrSafe Function InternetSetOption Lib "wininet.dll" Alias "InternetSetOptionA" _
        (ByVal hInternet As LongPtr, ByVal dwOption As Long, ByVal lpBuffer As Any, ByVal dwBufferLength As Long) As Boolean
#Else
    Private Declare Function InternetSetOption Lib "wininet.dll" Alias "InternetSetOptionA" _
        (ByVal hInternet As Long, ByVal dwOption As Long, ByVal lpBuffer As Any, ByVal dwBufferLength As Long) As Boolean
#End If

Private Const INTERNET_OPTION_SECURITY_FLAGS As Long = 31
Private Const SECURITY_FLAG_IGNORE_UNKNOWN_CA As Long = &H100

' ========== ENABLE TLS 1.2 ==========
Sub EnableTLS()
    ' Enable TLS 1.2 for XMLHTTP requests
    Call InternetSetOption(0, INTERNET_OPTION_SECURITY_FLAGS, SECURITY_FLAG_IGNORE_UNKNOWN_CA, 4)
End Sub

' ========== TEST CONNECTION WITH TLS ==========
Sub TestConnectionWithTLS()
    Dim http As Object
    Dim url As String
    Dim response As String
    
    ' Enable TLS first
    Call EnableTLS
    
    ' Build test URL
    url = WEB_APP_URL & "?sheet=MyData&startRow=1&startCol=1&numRows=5&numCols=5"
    
    ' Try WinHttp.WinHttpRequest first (better for HTTPS)
    On Error Resume Next
    Set http = CreateObject("WinHttp.WinHttpRequest.5.1")
    
    If http Is Nothing Then
        ' Fallback to MSXML2
        Set http = CreateObject("MSXML2.ServerXMLHTTP.6.0")
        If http Is Nothing Then
            Set http = CreateObject("MSXML2.XMLHTTP")
        End If
    End If
    On Error GoTo 0
    
    On Error GoTo ConnectionError
    
    ' Configure for HTTPS
    http.Open "GET", url, False
    http.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) Excel VBA"
    http.setRequestHeader "Accept", "*/*"
    
    ' Set timeout (30 seconds)
    On Error Resume Next
    http.setTimeouts 30000, 30000, 30000, 30000
    On Error GoTo ConnectionError
    
    ' Send request
    http.send
    
    ' Get response
    response = http.responseText
    
    ' Check result
    If http.Status = 200 Then
        If InStr(response, """status"":""success""") > 0 Then
            MsgBox "? CONNECTION SUCCESSFUL!" & vbCrLf & vbCrLf & _
                   "Status: " & http.Status & vbCrLf & _
                   "Response length: " & Len(response) & " chars" & vbCrLf & vbCrLf & _
                   "Preview:" & vbCrLf & Left(response, 200) & "...", _
                   vbInformation, "Success!"
        Else
            MsgBox "?? Connected but got unexpected response:" & vbCrLf & vbCrLf & _
                   Left(response, 500), vbExclamation, "Check Response"
        End If
    Else
        MsgBox "? HTTP Error: " & http.Status & vbCrLf & vbCrLf & _
               http.StatusText & vbCrLf & vbCrLf & _
               "Response: " & Left(response, 300), vbCritical, "Error"
    End If
    
    Exit Sub

ConnectionError:
    Dim errorMsg As String
    errorMsg = "? CONNECTION FAILED!" & vbCrLf & vbCrLf & _
               "Error: " & Err.Number & " - " & Err.Description & vbCrLf & vbCrLf
    
    Select Case Err.Number
        Case -2147024891, 5  ' Access denied
            errorMsg = errorMsg & "This is an ACCESS DENIED error." & vbCrLf & vbCrLf & _
                       "TRY THESE FIXES:" & vbCrLf & _
                       "1. Run Excel as Administrator" & vbCrLf & _
                       "2. Check your antivirus/firewall" & vbCrLf & _
                       "3. Use Alternative Method (see below)" & vbCrLf & _
                       "4. Check Internet Explorer security settings"
        Case -2147467259  ' Unspecified error
            errorMsg = errorMsg & "Try running Excel as Administrator"
        Case -2147012889  ' Certificate error
            errorMsg = errorMsg & "SSL/TLS Certificate issue" & vbCrLf & _
                       "The script is trying to use TLS fix..."
    End Select
    
    errorMsg = errorMsg & vbCrLf & vbCrLf & "URL: " & url
    
    MsgBox errorMsg, vbCritical, "Connection Error"
End Sub

' ========== MAIN IMPORT WITH TLS ==========
Sub ImportFromGoogleSheet()
    Dim http As Object
    Dim url As String
    Dim jsonResponse As String
    Dim ws As Worksheet
    Dim startTime As Double
    
    Application.ScreenUpdating = False
    startTime = Timer
    
    ' Enable TLS
    Call EnableTLS
    
    ' Build URL
    url = WEB_APP_URL & "?sheet=MyData&startRow=1&startCol=1&numRows=-1&numCols=-1"
    
    ' Use WinHttpRequest for better HTTPS support
    On Error Resume Next
    Set http = CreateObject("WinHttp.WinHttpRequest.5.1")
    If http Is Nothing Then
        Set http = CreateObject("MSXML2.ServerXMLHTTP.6.0")
        If http Is Nothing Then
            Set http = CreateObject("MSXML2.XMLHTTP")
        End If
    End If
    On Error GoTo ErrorHandler
    
    ' Configure request
    http.Open "GET", url, False
    http.setRequestHeader "User-Agent", "Excel VBA"
    
    ' Set timeouts
    On Error Resume Next
    http.setTimeouts 30000, 30000, 30000, 30000
    On Error GoTo ErrorHandler
    
    ' Send request
    http.send
    
    ' Check response
    If http.Status <> 200 Then
        MsgBox "? HTTP Error: " & http.Status, vbCritical
        GoTo Cleanup
    End If
    
    jsonResponse = http.responseText
    
    ' Check for errors
    If InStr(jsonResponse, """status"":""error""") > 0 Then
        MsgBox "? Script Error:" & vbCrLf & jsonResponse, vbCritical
        GoTo Cleanup
    End If
    
    ' Get/create worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets("GSheet2Exl")
    If ws Is Nothing Then
        Set ws = ThisWorkbook.Sheets.Add
        ws.Name = "GSheet2Exl"
    End If
    On Error GoTo ErrorHandler
    
    ' Import data
    ws.Cells.Clear
    Call ParseGoogleJSON(jsonResponse, ws)
    Call FormatSheet(ws)
    
    Application.ScreenUpdating = True
    
    MsgBox "? Success!" & vbCrLf & _
           "Time: " & Format(Timer - startTime, "0.00") & "s" & vbCrLf & _
           "Rows: " & ws.UsedRange.rows.count, vbInformation
    
    ws.Activate
    
Cleanup:
    Application.ScreenUpdating = True
    Exit Sub

ErrorHandler:
    Application.ScreenUpdating = True
    
    If Err.Number = -2147024891 Or Err.Number = 5 Then
        MsgBox "? ACCESS DENIED ERROR!" & vbCrLf & vbCrLf & _
               "FIXES:" & vbCrLf & _
               "1. Close Excel" & vbCrLf & _
               "2. Right-click Excel ? Run as Administrator" & vbCrLf & _
               "3. Try again" & vbCrLf & vbCrLf & _
               "OR use the Alternative Browser Method", _
               vbCritical, "Access Denied"
    Else
        MsgBox "? Error " & Err.Number & ":" & vbCrLf & Err.Description, vbCritical
    End If
End Sub

' ========== JSON PARSER ==========
Sub ParseGoogleJSON(jsonText As String, ws As Worksheet)
    Dim dataStart As Long, dataEnd As Long
    Dim dataSection As String
    Dim rows() As String
    Dim i As Long, row As Long
    
    dataStart = InStr(jsonText, """data"":")
    If dataStart = 0 Then Exit Sub
    
    dataStart = InStr(dataStart, jsonText, "[[")
    dataEnd = InStrRev(jsonText, "]]")
    dataSection = Mid(jsonText, dataStart + 1, dataEnd - dataStart)
    
    rows = Split(dataSection, "],[")
    
    row = 1
    For i = LBound(rows) To UBound(rows)
        rows(i) = Replace(rows(i), "[", "")
        rows(i) = Replace(rows(i), "]", "")
        Call ParseRow(rows(i), ws, row)
        row = row + 1
    Next i
End Sub

Sub ParseRow(rowText As String, ws As Worksheet, row As Long)
    Dim col As Long, i As Long
    Dim currentChar As String
    Dim cellValue As String
    Dim inQuotes As Boolean
    
    col = 1
    cellValue = ""
    inQuotes = False
    
    For i = 1 To Len(rowText)
        currentChar = Mid(rowText, i, 1)
        
        If currentChar = """" Then
            inQuotes = Not inQuotes
        ElseIf currentChar = "," And Not inQuotes Then
            ws.Cells(row, col).value = CleanValue(cellValue)
            col = col + 1
            cellValue = ""
        Else
            cellValue = cellValue & currentChar
        End If
    Next i
    
    If cellValue <> "" Or col > 1 Then
        ws.Cells(row, col).value = CleanValue(cellValue)
    End If
End Sub

Function CleanValue(value As String) As Variant
    Dim cleaned As String
    cleaned = Trim(value)
    
    If Left(cleaned, 1) = """" Then cleaned = Mid(cleaned, 2)
    If Right(cleaned, 1) = """" Then cleaned = Left(cleaned, Len(cleaned) - 1)
    
    If cleaned = "null" Then
        CleanValue = ""
    ElseIf IsNumeric(cleaned) Then
        CleanValue = CDbl(cleaned)
    Else
        CleanValue = Replace(cleaned, "\""", """")
    End If
End Function

Sub FormatSheet(ws As Worksheet)
    On Error Resume Next
    ws.Columns.AutoFit
    
    If ws.UsedRange.rows.count > 0 Then
        With ws.rows(1)
            .Font.Bold = True
            .Interior.Color = RGB(68, 114, 196)
            .Font.Color = RGB(255, 255, 255)
        End With
        ws.UsedRange.Borders.LineStyle = xlContinuous
    End If
End Sub

#########################################################################################

Option Explicit

' ===== GLOBAL VARIABLES - DECLARED PROPERLY =====
Private g_keys1 As Object, g_keys2 As Object
Private g_trueRow1 As Long, g_trueRow2 As Long

Sub SendMyDExlToGoogleCompare()
    Dim wsMy As Worksheet, wsGS As Worksheet
    Dim hasMismatch As Boolean
    Dim userChoice As VbMsgBoxResult
    
    Call ImportFromGoogleSheet
    Application.Wait 2000
    
    Set wsMy = ThisWorkbook.Worksheets("MyDExl")
    Set wsGS = ThisWorkbook.Worksheets("GSheet2Exl")
    
    hasMismatch = CompareMasterKeys(wsMy, wsGS)
    
    If Not hasMismatch Then
        MsgBox "? Perfect match! All records identical.", vbInformation
        Exit Sub
    Else
        Call GenerateMismatchReport(wsMy, wsGS)
        userChoice = MsgBox("?? MISMATCHES FOUND!" & vbCrLf & _
                           "Master Key report in 'MisMatchorError'." & vbCrLf & _
                           "Yes=Send | No=Review | Cancel=Exit", vbYesNoCancel + vbExclamation)
        
        Select Case userChoice
            Case vbCancel: Exit Sub
            Case vbNo
                ThisWorkbook.Worksheets("MisMatchorError").Activate
                If MsgBox("Send anyway?", vbYesNo) = vbNo Then Exit Sub
        End Select
    End If
    
SendData:
    Dim lastRow As Long, lastCol As Long, data As Variant
    Dim jsonBody As String, win As Object, webUrl As String
    
    On Error GoTo ErrorHandler
    lastRow = wsMy.Cells(wsMy.rows.count, "A").End(xlUp).row
    lastCol = wsMy.Cells(1, wsMy.Columns.count).End(xlToLeft).Column
    
    If lastRow < 1 Or lastCol < 1 Then
        MsgBox "? No data in MyDExl", vbCritical: Exit Sub
    End If
    
    data = wsMy.Range(wsMy.Cells(1, 1), wsMy.Cells(lastRow, lastCol)).value
    jsonBody = "{""values"":" & TwoDArrayToJsonSafe(data) & "}"
    webUrl = Trim$(ThisWorkbook.Worksheets("MasterCode").Range("D4").text)
    
    Set win = CreateObject("WinHttp.WinHttpRequest.5.1")
    win.Open "POST", webUrl, False
    win.setRequestHeader "Content-Type", "application/json"
    win.send jsonBody
    
    If win.Status = 200 Then
        MsgBox "? SUCCESS! " & lastRow & "x" & lastCol & " rows sent.", vbInformation
    Else
        MsgBox "? HTTP Error " & win.Status & vbCrLf & win.responseText, vbCritical
    End If
    Exit Sub
    
ErrorHandler:
    MsgBox "? VBA Error " & Err.Number & ": " & Err.Description, vbCritical
End Sub

Private Function CompareMasterKeys(ws1 As Worksheet, ws2 As Worksheet) As Boolean
    Dim keys1 As Object, keys2 As Object
    Dim trueRow1 As Long, trueRow2 As Long, r As Long
    Dim masterKey As String
    
    ' ? INITIALIZE DICTIONARIES
    Set keys1 = CreateObject("Scripting.Dictionary")
    Set keys2 = CreateObject("Scripting.Dictionary")
    
    trueRow1 = GetTrueLastRow(ws1)
    trueRow2 = GetTrueLastRow(ws2)
    
    ' Generate keys for Sheet1
    For r = 2 To trueRow1
        masterKey = GenerateMasterKey(ws1, r)
        If masterKey <> "" Then keys1(masterKey) = r
    Next r
    
    ' Generate keys for Sheet2
    For r = 2 To trueRow2
        masterKey = GenerateMasterKey(ws2, r)
        If masterKey <> "" Then keys2(masterKey) = r
    Next r
    
    ' ? STORE GLOBALLY FOR REPORT
    Set g_keys1 = keys1
    Set g_keys2 = keys2
    g_trueRow1 = trueRow1
    g_trueRow2 = trueRow2
    
    ' Compare: Same number of unique keys = match
    CompareMasterKeys = (keys1.count = keys2.count)
End Function

Private Function GenerateMasterKey(ws As Worksheet, rowNum As Long) As String
    Dim particulars As String, category As String, sizeVal As String
    particulars = Trim(CStr(ws.Cells(rowNum, 1).value))  ' Column A - Particulars
    category = Trim(CStr(ws.Cells(rowNum, 2).value))     ' Column B - Category
    sizeVal = Trim(CStr(ws.Cells(rowNum, 3).value))      ' Column C - Size
    
    If particulars <> "" Then
        GenerateMasterKey = particulars & "|" & category & "|" & sizeVal
    Else
        GenerateMasterKey = ""
    End If
End Function

Private Sub GenerateMismatchReport(ws1 As Worksheet, ws2 As Worksheet)
    Dim wsError As Worksheet, outputRow As Long
    Dim key As Variant
    
    ' Create/clear sheet
    On Error Resume Next
    Set wsError = ThisWorkbook.Worksheets("MisMatchorError")
    On Error GoTo 0
    
    If wsError Is Nothing Then
        Set wsError = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.count))
        wsError.Name = "MisMatchorError"
    Else
        wsError.Cells.Clear
    End If
    
    wsError.Activate
    Application.ScreenUpdating = False
    
    outputRow = 1
    
    ' ===== SHEET1 ONLY (Not in Sheet2) =====
    wsError.Cells(outputRow, 1).value = "SHEET1 ONLY (Not in Sheet2)"
    wsError.Range("A" & outputRow & ":E" & outputRow).Merge
    wsError.Range("A" & outputRow).Font.Bold = True
    wsError.Range("A" & outputRow).Interior.Color = RGB(255, 199, 206)
    outputRow = outputRow + 2
    
    ' Headers
    wsError.Cells(outputRow, 1) = "Row": wsError.Cells(outputRow, 2) = "Particulars"
    wsError.Cells(outputRow, 3) = "Category": wsError.Cells(outputRow, 4) = "Size"
    wsError.Cells(outputRow, 5) = "MasterKey"
    With wsError.Range("A" & outputRow & ":E" & outputRow)
        .Font.Bold = True: .Interior.Color = RGB(184, 204, 228)
        .HorizontalAlignment = xlCenter
    End With
    outputRow = outputRow + 1
    
    Dim count1 As Long: count1 = 0
    For Each key In g_keys1.Keys
        If Not g_keys2.Exists(key) Then
            wsError.Cells(outputRow, 1) = g_keys1(key)
            Dim keyParts() As String: keyParts = Split(CStr(key), "|")
            wsError.Cells(outputRow, 2) = keyParts(0)
            wsError.Cells(outputRow, 3) = keyParts(1)
            wsError.Cells(outputRow, 4) = keyParts(2)
            wsError.Cells(outputRow, 5) = key
            wsError.Range("A" & outputRow & ":E" & outputRow).Interior.Color = RGB(255, 235, 156)
            outputRow = outputRow + 1: count1 = count1 + 1
        End If
    Next
    
    outputRow = outputRow + 3
    
    ' ===== SHEET2 ONLY (Not in Sheet1) =====
    wsError.Cells(outputRow, 1).value = "SHEET2 ONLY (Not in Sheet1)"
    wsError.Range("A" & outputRow & ":E" & outputRow).Merge
    wsError.Range("A" & outputRow).Font.Bold = True
    wsError.Range("A" & outputRow).Interior.Color = RGB(255, 199, 206)
    outputRow = outputRow + 2
    
    wsError.Cells(outputRow, 1) = "Row": wsError.Cells(outputRow, 2) = "Particulars"
    wsError.Cells(outputRow, 3) = "Category": wsError.Cells(outputRow, 4) = "Size"
    wsError.Cells(outputRow, 5) = "MasterKey"
    With wsError.Range("A" & outputRow & ":E" & outputRow)
        .Font.Bold = True: .Interior.Color = RGB(184, 204, 228)
        .HorizontalAlignment = xlCenter
    End With
    outputRow = outputRow + 1
    
    Dim count2 As Long: count2 = 0
    For Each key In g_keys2.Keys
        If Not g_keys1.Exists(key) Then
            wsError.Cells(outputRow, 1) = g_keys2(key)
            keyParts = Split(CStr(key), "|")
            wsError.Cells(outputRow, 2) = keyParts(0)
            wsError.Cells(outputRow, 3) = keyParts(1)
            wsError.Cells(outputRow, 4) = keyParts(2)
            wsError.Cells(outputRow, 5) = key
            wsError.Range("A" & outputRow & ":E" & outputRow).Interior.Color = RGB(255, 235, 156)
            outputRow = outputRow + 1: count2 = count2 + 1
        End If
    Next
    
    ' Summary
    outputRow = outputRow + 2
    wsError.Cells(outputRow, 1).value = "SUMMARY:"
    wsError.Cells(outputRow, 1).Font.Bold = True: wsError.Cells(outputRow, 2).value = "Sheet1 Only: " & count1
    wsError.Cells(outputRow, 3).value = "Sheet2 Only: " & count2
    wsError.Cells(outputRow, 4).value = "Total Mismatches: " & (count1 + count2)
    
    wsError.Columns("A:E").AutoFit
    Application.ScreenUpdating = True
    
    MsgBox "REPORT:" & vbCrLf & "Sheet1 Only: " & count1 & vbCrLf & "Sheet2 Only: " & count2, vbExclamation
End Sub

Private Function GetTrueLastRow(ws As Worksheet) As Long
    Dim r As Long
    For r = ws.rows.count To 1 Step -1
        If Trim(CStr(ws.Cells(r, 1).value)) <> "" Or Trim(CStr(ws.Cells(r, 2).value)) <> "" Then
            GetTrueLastRow = r: Exit Function
        End If
    Next r
    GetTrueLastRow = 1
End Function

Private Function TwoDArrayToJsonSafe(arr As Variant) As String
    Dim r As Long, c As Long, rows() As String, cols() As String, cellVal As String
    ReDim rows(1 To UBound(arr, 1))
    For r = 1 To UBound(arr, 1)
        ReDim cols(1 To UBound(arr, 2))
        For c = 1 To UBound(arr, 2)
            cellVal = CleanJsonString(CStr(arr(r, c)))
            cols(c) = cellVal
        Next c
        rows(r) = "[" & Join(cols, ",") & "]"
    Next r
    TwoDArrayToJsonSafe = "[" & Join(rows, ",") & "]"
End Function

Private Function CleanJsonString(inputStr As String) As String
    Dim i As Long, output As String, ch As String
    output = ""
    For i = 1 To Len(inputStr)
        ch = Mid(inputStr, i, 1)
        Select Case ch
            Case """": output = output & "\"""
            Case "\": output = output & "\\"
            Case vbCr: output = output & "\r"
            Case vbLf: output = output & "\n"
            Case vbTab: output = output & "\t"
            Case Else: output = output & ch
        End Select
    Next i
    CleanJsonString = """" & output & """"
End Function

####################################################################################

Export excel data to google sheet 

function doGet(e) {
  try {
    const ss = SpreadsheetApp.openById('sheetidorserialkeyweburl-000000');
    const sh = ss.getActiveSheet();
    
    // FORCE get ALL data A1:Z1000 even if empty
    const values = sh.getRange("A1:Z1000").getValues();
    
    // Filter out completely empty rows
    const cleanData = values.filter(row => row.some(cell => cell !== ""));
    
    Logger.log("Data rows: " + cleanData.length);
    
    return ContentService
      .createTextOutput(JSON.stringify(cleanData))
      .setMimeType(ContentService.MimeType.JSON);
  } catch(e) {
    return ContentService.createTextOutput('ERROR: ' + e.toString()).setMimeType(ContentService.MimeType.TEXT);
  }
}

#########################################################################################
Import Data from Google sheet to excel

// ========= WEB APP FOR EXCEL POST =========
// Receives JSON from Excel and writes it into this spreadsheet.

function doPost(e) {
  try {
    var raw = e.postData && e.postData.contents ? e.postData.contents : "";
    if (!raw) {
      return jsonResponse({ status: "error", message: "Empty body" });
    }

    var payload = JSON.parse(raw);       // { sheet, range, values }
    var sheetName = payload.sheet || "MyData";
    var startA1   = payload.range || "A1";
    var values    = payload.values;

    if (!values || !values.length) {
      return jsonResponse({ status: "error", message: "No values in payload" });
    }

    // Use the spreadsheet this script is bound to
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sh = ss.getSheetByName(sheetName);
    if (!sh) {
      sh = ss.insertSheet(sheetName);
    }

    // Clear existing data (optional)
    sh.clearContents();

    var rows = values.length;
    var cols = values[0].length;

    var rng = sh.getRange(startA1).offset(0, 0, rows, cols);
    rng.setValues(values);   // <-- THIS ACTUALLY WRITES THE DATA

    return jsonResponse({
      status: "success",
      spreadsheetName: ss.getName(),
      sheet: sheetName,
      rows: rows,
      cols: cols
    });

  } catch (err) {
    return jsonResponse({ status: "error", message: err.toString() });
  }
}

// Helper: return JSON
function jsonResponse(obj) {
  return ContentService
    .createTextOutput(JSON.stringify(obj))
    .setMimeType(ContentService.MimeType.JSON);
}


// ============================================
// Google Apps Script - Fixed Upload Handler
// ============================================

// ========== GET REQUEST - Download from Google ==========
function doGet(e) {
  try {
    var sheetName = e.parameter.sheet || 'MyData';
    var startRow = parseInt(e.parameter.startRow) || 1;
    var startCol = parseInt(e.parameter.startCol) || 1;
    var numRows = parseInt(e.parameter.numRows) || -1;
    var numCols = parseInt(e.parameter.numCols) || -1;
    
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName(sheetName);
    
    if (!sheet) {
      return createResponse({
        'status': 'error',
        'message': 'Sheet not found: ' + sheetName
      });
    }
    
    var lastRow = sheet.getLastRow();
    var lastCol = sheet.getLastColumn();
    
    if (lastRow === 0 || lastCol === 0) {
      return createResponse({
        'status': 'error',
        'message': 'Sheet is empty'
      });
    }
    
    var endRow = (numRows === -1) ? lastRow : Math.min(startRow + numRows - 1, lastRow);
    var endCol = (numCols === -1) ? lastCol : Math.min(startCol + numCols - 1, lastCol);
    
    var range = sheet.getRange(startRow, startCol, endRow - startRow + 1, endCol - startCol + 1);
    var values = range.getValues();
    
    return createResponse({
      'status': 'success',
      'sheetName': sheetName,
      'rowCount': values.length,
      'colCount': values[0] ? values[0].length : 0,
      'data': values,
      'timestamp': new Date().toISOString()
    });
    
  } catch (error) {
    return createResponse({
      'status': 'error',
      'message': error.toString()
    });
  }
}

// ========== POST REQUEST - Upload from Excel ==========
function doPost(e) {
  try {
    // Log incoming request for debugging
    Logger.log('POST request received');
    Logger.log('postData: ' + JSON.stringify(e.postData));
    
    // Check if data exists
    if (!e.postData || !e.postData.contents) {
      return createResponse({
        'status': 'error',
        'message': 'No post data received',
        'received': JSON.stringify(e)
      });
    }
    
    // Parse JSON data
    var jsonData;
    try {
      jsonData = JSON.parse(e.postData.contents);
      Logger.log('Parsed JSON: ' + JSON.stringify(jsonData));
    } catch (parseError) {
      return createResponse({
        'status': 'error',
        'message': 'Invalid JSON format',
        'error': parseError.toString(),
        'receivedData': e.postData.contents
      });
    }
    
    // Extract parameters
    var sheetName = jsonData.sheetName || 'MyData';
    var data = jsonData.data;
    var clearExisting = jsonData.clearExisting !== false; // default true
    var startRow = jsonData.startRow || 1;
    var startCol = jsonData.startCol || 1;
    
    // Validate data
    if (!data || !Array.isArray(data)) {
      return createResponse({
        'status': 'error',
        'message': 'No valid data array provided',
        'receivedData': typeof data
      });
    }
    
    if (data.length === 0) {
      return createResponse({
        'status': 'error',
        'message': 'Data array is empty'
      });
    }
    
    // Get spreadsheet and sheet
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName(sheetName);
    
    // Create sheet if it doesn't exist
    if (!sheet) {
      Logger.log('Creating new sheet: ' + sheetName);
      sheet = ss.insertSheet(sheetName);
    }
    
    // Clear existing data if requested
    if (clearExisting) {
      Logger.log('Clearing existing data');
      sheet.clear();
    }
    
    // Write data
    var numRows = data.length;
    var numCols = data[0].length;
    
    Logger.log('Writing ' + numRows + ' rows and ' + numCols + ' columns');
    
    var range = sheet.getRange(startRow, startCol, numRows, numCols);
    range.setValues(data);
    
    // Auto-resize columns
    for (var col = startCol; col < startCol + numCols; col++) {
      sheet.autoResizeColumn(col);
    }
    
    // Format header row if starting from row 1
    if (startRow === 1 && numRows > 0) {
      var headerRange = sheet.getRange(1, startCol, 1, numCols);
      headerRange.setFontWeight('bold');
      headerRange.setBackground('#4a86e8');
      headerRange.setFontColor('#ffffff');
      headerRange.setHorizontalAlignment('center');
    }
    
    // Add borders
    range.setBorder(true, true, true, true, true, true);
    
    Logger.log('Upload completed successfully');
    
    return createResponse({
      'status': 'success',
      'message': 'Data uploaded successfully',
      'spreadsheetName': ss.getName(),
      'sheetName': sheetName,
      'rowsWritten': numRows,
      'colsWritten': numCols,
      'startRow': startRow,
      'startCol': startCol,
      'timestamp': new Date().toISOString()
    });
    
  } catch (error) {
    Logger.log('Error in doPost: ' + error.toString());
    return createResponse({
      'status': 'error',
      'message': 'Error processing upload',
      'error': error.toString(),
      'stack': error.stack
    });
  }
}

// Helper function to create JSON response
function createResponse(data) {
  var output = ContentService.createTextOutput(JSON.stringify(data));
  output.setMimeType(ContentService.MimeType.JSON);
  return output;
}

// ========== TEST FUNCTIONS ==========

// Test GET
function testGet() {
  var testEvent = {
    parameter: {
      sheet: 'MyData',
      startRow: '1',
      startCol: '1',
      numRows: '5',
      numCols: '5'
    }
  };
  
  var result = doGet(testEvent);
  Logger.log('GET Result: ' + result.getContent());
}

// Test POST
function testPost() {
  var testData = {
    postData: {
      contents: JSON.stringify({
        sheetName: 'TestUpload',
        clearExisting: true,
        startRow: 1,
        startCol: 1,
        data: [
          ['Name', 'Age', 'City'],
          ['John', 25, 'New York'],
          ['Jane', 30, 'London'],
          ['Bob', 35, 'Paris']
        ]
      }),
      type: 'application/json'
    }
  };
  
  var result = doPost(testData);
  Logger.log('POST Result: ' + result.getContent());
}
